<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
//Apti财务市场部分管理
class Aptimarket extends Common
{
	public function market_activity($page=1,$list=20){

		$request = Request::instance();$data=$request->post();

		$whereap="ap.is_shengxiao=1 and ap.state in(7,9) and ap.tag=0 and ap.is_actived=1 and ap.is_deleted=0 and apd.is_actived=1 and apd.is_deleted=0 ";
		$whereti="ap.status in(7,9) and ap.status2 is null ";
		if($data){
			if(trim($data["ap_no"])){
				$tmp=explode('_', trim($data["ap_no"]));
				if(count($tmp)==1){
					$whereap.="and ap.no='{$tmp[0]}' ";
					$whereti.="and ap.sn='{$tmp[0]}' ";
				}else if(count($tmp)==2){
					$whereap.="and ap.no='{$tmp[0]}' and apd.detail_no='{$tmp[1]}' ";
					$whereti.="and ap.sn='{$tmp[0]}' and apd.sn='{$tmp[1]}' ";
				}
			}
			if($data["start"]){
				$whereap.="and ap.created_on>='{$data["start"]}' ";
				$stamp=strtotime($data["start"]);
				$whereti.="and ap.ctime>=$stamp ";
			}
			if($data["end"]){
				$stamp=strtotime($data["end"])+3600*24;
				$end=date("Y-m-d",$stamp);
				$whereap.="and ap.created_on<'{$end}' ";
				$whereti.="and ap.ctime<$stamp ";
			}
		}else{
			$data=["start"=>"","end"=>"","ap_no"=>"","type"=>0];
		}
		$this->assign("data",$data);
		//echo $data["type"];
		$apall=[];
		if($data["type"]==1||$data["type"]==0){
			$apall=Db::table("ap_apply")->alias("ap")
			->join("ap_apply_detail apd","apd.apply_id=ap.id")
			->join("apti_zhifu_list zf","zf.apply_detail_id=apd.id","LEFT")
			->join("users u","ap.c_users_id=u.id")
			->join("employee e","u.employee_id=e.id")
			->group("apd.id")
			->where($whereap)
			->order("ap.created_on desc")
			->field("apd.id,e.em_name,ap.no,apd.detail_no as detail_no,apd.pifuzonge,apd.shuilv,apd.buyu_he,apd.hexiao,sum(zf.bcjine) as zhifu")
			->select();
		}
		$tiall=[];
		if($data["type"]==2||$data["type"]==0){
			$tiall=Db::table("cg_ti")->alias("ap")
			->join("cg_ti_list apd","apd.ti_id=ap.id")
			->join("apti_zhifu_list zf","zf.apply_detail_id=apd.id","LEFT")
			->join("employee e","ap.cuser=e.id")
			->group("apd.id")
			->where($whereti)
			->order("ap.ctime desc")
			->field("apd.id,e.em_name,ap.sn as no,apd.sn as detail_no,apd.pizhunhanshui,apd.fb_shijitourufeiyong,apd.hx_byhx,apd.hx_yihexiao,sum(zf.bcjine) as zhifu,apd.hx_status")
			->select();
		}
		$all=[];
		//合并ap
		for($i=0;$i<count($apall);$i++){
			$tmp["id"]=$apall[$i]["id"];
			$tmp["type"]="AP";$tmp["em_name"]=$apall[$i]["em_name"];$tmp["no"]=$apall[$i]["no"].'_'.$apall[$i]["detail_no"];
			$tmp["pizhun"]=$apall[$i]["pifuzonge"]+$apall[$i]["pifuzonge"]*$apall[$i]["shuilv"]/100;
			$tmp["fankui"]=0;
		
			$tmp["buyu_he"]=$apall[$i]["buyu_he"];
			$tmp["zhifu"]=$apall[$i]["zhifu"];
			$all[]=$tmp;
		}
		//合并ti
		for($i=0;$i<count($tiall);$i++){
			$tmp["id"]=$tiall[$i]["id"];
			$tmp["type"]="TI";$tmp["em_name"]=$tiall[$i]["em_name"];$tmp["no"]=$tiall[$i]["no"].'_'.$tiall[$i]["detail_no"];
			$tmp["pizhun"]=$tiall[$i]["pizhunhanshui"];
			$tmp["fankui"]=$tiall[$i]["fb_shijitourufeiyong"]-$tmp["pizhun"];
			
			$tmp["hx_status"]=$tiall[$i]["hx_status"];
			$tmp["zhifu"]=$tiall[$i]["zhifu"];
			$all[]=$tmp;
		}
		//print_r($tiall);
		$sum=count($all);
		$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $end=$start+$list;
        if($end>=$sum)
        	$end=$sum;
        $detail=[];
        for($i=$start;$i<$end;$i++){
        	$det=$all[$i];
        	if($det["type"]=="AP"){
        		$det["hexiao"]=Db::table("ap_hexiao_apply")->alias("ahp")->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")->where("ahp.is_shengxiao=1 and ahp.state in(7,9) and ahd.detail_id={$det["id"]}")->sum("hexiao_weishui");
        		$det["yufuyue"]=Db::table("ap_hexiao_apply")->alias("ahp")->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")->where("ahp.is_shengxiao=1 and ahp.state in(7,9) and ahd.detail_id={$det["id"]} and ahd.has_fp=0")->sum("hexiao_weishui");
        		$det["zhuan"]=Db::table("ap_hexiao_apply")->alias("ahp")->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")->where("ahp.is_shengxiao=1 and ahp.state in(7,9) and ahd.detail_id={$det["id"]} and ahd.has_fp=1 and ahd.bu_state=2")->sum("hexiao_weishui");
        		$det["yufu"]=$det["yufuyue"]+$det["zhuan"];
        		

        			if($det["buyu_he"]){
						$det["buhe"]=$det["hexiao"]-$det["pizhun"];
					}else{
						$det["buhe"]=0;
					}
					$det["yutizong"]=$det["pizhun"]+$det["buhe"];
					$det["yutiyu"]=$det["yutizong"]-$det["hexiao"];
        	}else{
        		$det["hexiao"]=Db::table("cg_ti_hexiao")->alias("ahp")->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")->where("ahp.status in (7,9) and ahd.list_id={$det["id"]}")->sum("ahd.weishuihexiaojine");
        		$det["yufuyue"]=Db::table("cg_ti_hexiao")->alias("ahp")->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")->where("ahp.status in (7,9) and ahd.list_id={$det["id"]} and ahd.youwufapiao=0")->sum("ahd.weishuihexiaojine");
        		$det["zhuan"]=Db::table("cg_ti_hexiao")->alias("ahp")->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")->where("ahp.status in (7,9) and ahd.list_id={$det["id"]} and ahd.bu_state=2")->sum("ahd.weishuihexiaojine");

        			if($det["hx_status"]==2){
						$det["buhe"]=$det["hexiao"]-$det["pizhun"];
					}else{
						$det["buhe"]=0;
					}
					$det["yutizong"]=$det["pizhun"]+$det["buhe"]+$det["fankui"];
        		$det["yufu"]=$det["yufuyue"]+$det["zhuan"];
        		$det["yutiyu"]=$det["yutizong"]-$det["hexiao"];

        	}
        	$detail[]=$det;
        }
        $this->assign("detail",$detail);
        $this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		$datastr=json_encode($data);
		$this->assign("datastr",$datastr);
		return $this->fetch();
	}
	//导出
	public function daochuma($datastr){
		$data=json_decode($datastr,true);
		$whereap="ap.is_shengxiao=1 and ap.state in(7,9) and ap.tag=0 and ap.is_actived=1 and ap.is_deleted=0 and apd.is_actived=1 and apd.is_deleted=0 ";
		$whereti="ap.status in(7,9) and ap.status2 is null ";
		if($data){
			if(trim($data["ap_no"])){
				$tmp=explode('_', trim($data["ap_no"]));
				if(count($tmp)==1){
					$whereap.="and ap.no='{$tmp[0]}' ";
					$whereti.="and ap.sn='{$tmp[0]}' ";
				}else if(count($tmp)==2){
					$whereap.="and ap.no='{$tmp[0]}' and apd.detail_no='{$tmp[1]}' ";
					$whereti.="and ap.sn='{$tmp[0]}' and apd.sn='{$tmp[1]}' ";
				}
			}
			if($data["start"]){
				$whereap.="and ap.created_on>='{$data["start"]}' ";
				$stamp=strtotime($data["start"]);
				$whereti.="and ap.ctime>=$stamp ";
			}
			if($data["end"]){
				$stamp=strtotime($data["end"])+3600*24;
				$end=date("Y-m-d",$stamp);
				$whereap.="and ap.created_on<'{$end}' ";
				$whereti.="and ap.ctime<$stamp ";
			}
		}else{
			$data=["start"=>"","end"=>"","ap_no"=>"","type"=>0];
		}
		$apall=[];
		if($data["type"]==1||$data["type"]==0){
			$apall=Db::table("ap_apply")->alias("ap")
			->join("ap_apply_detail apd","apd.apply_id=ap.id")
			->join("apti_zhifu_list zf","zf.apply_detail_id=apd.id","LEFT")
			->join("users u","ap.c_users_id=u.id")
			->join("employee e","u.employee_id=e.id")
			->group("apd.id")
			->where($whereap)
			->order("ap.created_on desc")
			->field("apd.id,e.em_name,ap.no,apd.detail_no as detail_no,apd.pifuzonge,apd.shuilv,apd.buyu_he,apd.hexiao,sum(zf.bcjine) as zhifu")
			->select();
		}
		$tiall=[];
		if($data["type"]==2||$data["type"]==0){
			$tiall=Db::table("cg_ti")->alias("ap")
			->join("cg_ti_list apd","apd.ti_id=ap.id")
			->join("apti_zhifu_list zf","zf.apply_detail_id=apd.id","LEFT")
			->join("employee e","ap.cuser=e.id")
			->group("apd.id")
			->where($whereti)
			->order("ap.ctime desc")
			->field("apd.id,e.em_name,ap.sn as no,apd.sn as detail_no,apd.pizhunhanshui,apd.fb_shijitourufeiyong,apd.hx_byhx,apd.hx_yihexiao,sum(zf.bcjine) as zhifu,apd.hx_status")
			->select();
		}
		$all=[];
		//合并ap
		for($i=0;$i<count($apall);$i++){
			$tmp["id"]=$apall[$i]["id"];
			$tmp["type"]="AP";$tmp["em_name"]=$apall[$i]["em_name"];$tmp["no"]=$apall[$i]["no"].'_'.$apall[$i]["detail_no"];
			$tmp["pizhun"]=$apall[$i]["pifuzonge"]+$apall[$i]["pifuzonge"]*$apall[$i]["shuilv"]/100;
			$tmp["fankui"]=0;
		
			$tmp["buyu_he"]=$apall[$i]["buyu_he"];
			$tmp["zhifu"]=$apall[$i]["zhifu"];
			$all[]=$tmp;
		}
		//合并ti
		for($i=0;$i<count($tiall);$i++){
			$tmp["id"]=$tiall[$i]["id"];
			$tmp["type"]="TI";$tmp["em_name"]=$tiall[$i]["em_name"];$tmp["no"]=$tiall[$i]["no"].'_'.$tiall[$i]["detail_no"];
			$tmp["pizhun"]=$tiall[$i]["pizhunhanshui"];
			$tmp["fankui"]=$tiall[$i]["fb_shijitourufeiyong"]-$tmp["pizhun"];
			
			$tmp["hx_status"]=$tiall[$i]["hx_status"];
			$tmp["zhifu"]=$tiall[$i]["zhifu"];
			$all[]=$tmp;
		}
		 $detail=[];
        for($i=0;$i<count($all);$i++){
        	$det=$all[$i];
        	if($det["type"]=="AP"){
        		$det["hexiao"]=Db::table("ap_hexiao_apply")->alias("ahp")->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")->where("ahp.is_shengxiao=1 and ahp.state in(7,9) and ahd.detail_id={$det["id"]}")->sum("hexiao_weishui");
        		$det["yufuyue"]=Db::table("ap_hexiao_apply")->alias("ahp")->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")->where("ahp.is_shengxiao=1 and ahp.state in(7,9) and ahd.detail_id={$det["id"]} and ahd.has_fp=0")->sum("hexiao_weishui");
        		$det["zhuan"]=Db::table("ap_hexiao_apply")->alias("ahp")->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")->where("ahp.is_shengxiao=1 and ahp.state in(7,9) and ahd.detail_id={$det["id"]} and ahd.has_fp=1 and ahd.bu_state=2")->sum("hexiao_weishui");
        		$det["yufu"]=$det["yufuyue"]+$det["zhuan"];
        		

        			if($det["buyu_he"]){
						$det["buhe"]=$det["hexiao"]-$det["pizhun"];
					}else{
						$det["buhe"]=0;
					}
					$det["yutizong"]=$det["pizhun"]+$det["buhe"];
					$det["yutiyu"]=$det["yutizong"]-$det["hexiao"];
        	}else{
        		$det["hexiao"]=Db::table("cg_ti_hexiao")->alias("ahp")->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")->where("ahp.status in (7,9) and ahd.list_id={$det["id"]}")->sum("ahd.weishuihexiaojine");
        		$det["yufuyue"]=Db::table("cg_ti_hexiao")->alias("ahp")->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")->where("ahp.status in (7,9) and ahd.list_id={$det["id"]} and ahd.youwufapiao=0")->sum("ahd.weishuihexiaojine");
        		$det["zhuan"]=Db::table("cg_ti_hexiao")->alias("ahp")->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")->where("ahp.status in (7,9) and ahd.list_id={$det["id"]} and ahd.bu_state=2")->sum("ahd.weishuihexiaojine");

        			if($det["hx_status"]==2){
						$det["buhe"]=$det["hexiao"]-$det["pizhun"];
					}else{
						$det["buhe"]=0;
					}
					$det["yutizong"]=$det["pizhun"]+$det["buhe"]+$det["fankui"];
        		$det["yufu"]=$det["yufuyue"]+$det["zhuan"];
        		$det["yutiyu"]=$det["yutizong"]-$det["hexiao"];

        	}
        	$detail[]=$det;
        }
        $eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();
	  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("市场活动对账");

          $objPHPExcel->getActiveSheet()->mergeCells('A1:M1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "市场活动对账");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:M2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);


         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);$objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
	    
	     $objPHPExcel->getActiveSheet()->getStyle('A3:M4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	     $objPHPExcel->getActiveSheet()->getStyle('A3:M4')->getFill()->getStartColor()->setARGB('0099CCFF');
	         //水平剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A3:M4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A3:M4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

	    $objPHPExcel->getActiveSheet()->setCellValue('A3',"申请人");
	    $objPHPExcel->getActiveSheet()->mergeCells('A3:A4');
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"市场活动明细编号");
        $objPHPExcel->getActiveSheet()->mergeCells('B3:B4');
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"类型");
        $objPHPExcel->getActiveSheet()->mergeCells('C3:C4');
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"预提汇总");
        $objPHPExcel->getActiveSheet()->mergeCells('D3:G3');
        $objPHPExcel->getActiveSheet()->setCellValue('D4',"预提申请");      
        $objPHPExcel->getActiveSheet()->setCellValue('E4',"反馈");
        $objPHPExcel->getActiveSheet()->setCellValue('F4',"不予核销");
        $objPHPExcel->getActiveSheet()->setCellValue('G4',"预提总额");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"核销总额");
	    $objPHPExcel->getActiveSheet()->mergeCells('H3:H4');

        $objPHPExcel->getActiveSheet()->setCellValue('I3',"支付总额");
         $objPHPExcel->getActiveSheet()->mergeCells('I3:I4');

        $objPHPExcel->getActiveSheet()->setCellValue('J3',"预付总额");
         $objPHPExcel->getActiveSheet()->mergeCells('J3:J4');

        $objPHPExcel->getActiveSheet()->setCellValue('K3',"预付转支付总额");
         $objPHPExcel->getActiveSheet()->mergeCells('K3:K4');

        $objPHPExcel->getActiveSheet()->setCellValue('L3',"预付余额");
         $objPHPExcel->getActiveSheet()->mergeCells('L3:L4');

        $objPHPExcel->getActiveSheet()->setCellValue('M3',"预提余额");
         $objPHPExcel->getActiveSheet()->mergeCells('M3:M4');

          $h=4;
        for($i=0;$i<count($detail);$i++){
        	$h++;$tmp=$detail[$i];
        	$objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$tmp["em_name"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('B'.$h,$tmp["no"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('C'.$h,$tmp["type"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('D'.$h,$tmp["pizhun"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('E'.$h,$tmp["fankui"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('F'.$h,$tmp["buhe"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('G'.$h,$tmp["yutizong"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('H'.$h,$tmp["hexiao"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('I'.$h,$tmp["zhifu"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('J'.$h,$tmp["yufu"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('K'.$h,$tmp["zhuan"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('L'.$h,$tmp["yufuyue"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('M'.$h,$tmp["yutiyu"]);
        }
          $hj=$h+1;
         $objPHPExcel->getActiveSheet()->getStyle($hj)->getAlignment()->setWrapText(true);
	    
	     $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':M'.$hj)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	     $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':M'.$hj)->getFill()->getStartColor()->setARGB('00FFCC99');
	         //水平剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':M'.$hj)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':M'.$hj)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

	     $objPHPExcel->getActiveSheet()->setCellValue('A'.$hj,"合计");
	    $objPHPExcel->getActiveSheet()->mergeCells('A'.$hj.':C'.$hj);
	      $objPHPExcel->getActiveSheet()->getStyle('A'.$hj)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
	      $objPHPExcel->getActiveSheet()->setCellValue('D'.$hj, '=SUM(D4:D'.$h.')');
	      $objPHPExcel->getActiveSheet()->setCellValue('E'.$hj, '=SUM(E4:E'.$h.')');
	      $objPHPExcel->getActiveSheet()->setCellValue('F'.$hj, '=SUM(F4:F'.$h.')');
	      $objPHPExcel->getActiveSheet()->setCellValue('G'.$hj, '=SUM(G4:G'.$h.')');
	      $objPHPExcel->getActiveSheet()->setCellValue('H'.$hj, '=SUM(H4:H'.$h.')');
	      $objPHPExcel->getActiveSheet()->setCellValue('I'.$hj, '=SUM(I4:I'.$h.')');
	      $objPHPExcel->getActiveSheet()->setCellValue('J'.$hj, '=SUM(J4:J'.$h.')');
	      $objPHPExcel->getActiveSheet()->setCellValue('K'.$hj, '=SUM(K4:K'.$h.')');
	     $objPHPExcel->getActiveSheet()->setCellValue('L'.$hj, '=SUM(L4:L'.$h.')');
	     $objPHPExcel->getActiveSheet()->setCellValue('M'.$hj, '=SUM(M4:M'.$h.')');



           $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(30);



             $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:M'.$hj)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        
          $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=市场活动对账.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}

	public function zongzhang($page=1,$list=10){
		$request = Request::instance();$data=$request->post();
		$caozuo=Db::table("market_caozuo")->select();
		$where="1=1 ";
		if($data){
			if(isset($data["caozuo"])){
				$strcaozuo=implode(',', $data["caozuo"]);

				$where.="and md.caozuo in ($strcaozuo) ";
			}else{
				$data["caozuo"]=[];
			}
			if(trim($data["bencino"])){
				$bencino=trim($data["bencino"]);
				$where.="and md.bencino like '$bencino%' ";
			}
			if(trim($data["duiyingno"])){
				$duiyingno=trim($data["duiyingno"]);
				$where.="and md.duiyingno like '$duiyingno%' ";
			}
			if($data["start"]){
				$where.="and md.jiruday>='{$data["start"]}' ";
			}
			if($data["end"]){
				$where.="and md.jiruday<='{$data["end"]}' ";
			}
			if($data["type"])
				$where.="and md.type={$data["type"]} ";
		}else{
			$data=["bencino"=>"","duiyingno"=>"","start"=>"","end"=>"","type"=>0,"caozuo"=>[]];
		}
		for($i=0;$i<count($caozuo);$i++){
			if(in_array($caozuo[$i]["id"],$data["caozuo"])){
				$caozuo[$i]["check"]=1;
			}else{
				$caozuo[$i]["check"]=0;
			}
		}
		$sum=Db::table("market_detail")->alias("md")
		->join("market_caozuo mc","md.caozuo=mc.id")
		->join("employee e","e.id=md.cuser")
		->where($where)->count();
			$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

		$apply=Db::table("market_detail")->alias("md")
		->join("market_caozuo mc","md.caozuo=mc.id")
		->join("employee e","e.id=md.cuser")
		->where($where)->order("jiruday desc")->limit($start,$list)
		->field("md.bencino,md.duiyingno,md.type,md.jiruday,mc.title,e.em_name,md.jine,md.zengzhishui")
		->select();

		$this->assign("apply",$apply);
		$this->assign("caozuo",$caozuo);
		$this->assign("data",$data);
		
		
		$this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
			$datastr=json_encode($data);
		$this->assign("datastr",$datastr);
		return $this->fetch();
	}
	//导出总账
	public function daochuzong($datastr){
		$data=json_decode($datastr,true);
		$where="1=1 ";
		if($data){
			if($data["caozuo"]){
				$strcaozuo=implode(',', $data["caozuo"]);

				$where.="and md.caozuo in ($strcaozuo) ";
			}else{
				$data["caozuo"]=[];
			}
			if(trim($data["bencino"])){
				$bencino=trim($data["bencino"]);
				$where.="and md.bencino like '$bencino%' ";
			}
			if(trim($data["duiyingno"])){
				$duiyingno=trim($data["duiyingno"]);
				$where.="and md.duiyingno like '$duiyingno%' ";
			}
			if($data["start"]){
				$where.="and md.jiruday>='{$data["start"]}' ";
			}
			if($data["end"]){
				$where.="and md.jiruday<='{$data["end"]}' ";
			}
			if($data["type"])
				$where.="and md.type={$data["type"]} ";
		}
			$apply=Db::table("market_detail")->alias("md")
		->join("market_caozuo mc","md.caozuo=mc.id")
		->join("employee e","e.id=md.cuser")
		->where($where)->order("jiruday desc")
		->field("md.bencino,md.duiyingno,md.type,md.jiruday,mc.title,e.em_name,md.jine,md.zengzhishui")
		->select();
		    $eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();
	  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("总账对账明细列表");


          $objPHPExcel->getActiveSheet()->mergeCells('A1:H1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "总账对账明细列表");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:H2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);


         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);$objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
	    
	     $objPHPExcel->getActiveSheet()->getStyle('A3:H3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	     $objPHPExcel->getActiveSheet()->getStyle('A3:H3')->getFill()->getStartColor()->setARGB('0099CCFF');
	         //水平剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A3:H3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A3:H3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	      $objPHPExcel->getActiveSheet()->setCellValue('A3',"申请人");
	      $objPHPExcel->getActiveSheet()->setCellValue('B3',"本次明细编号");
	      $objPHPExcel->getActiveSheet()->setCellValue('C3',"对应市场费用申请编号");
	      $objPHPExcel->getActiveSheet()->setCellValue('D3',"计入日期");
	      $objPHPExcel->getActiveSheet()->setCellValue('E3',"费用类型");
	      $objPHPExcel->getActiveSheet()->setCellValue('F3',"类型");
	      $objPHPExcel->getActiveSheet()->setCellValue('G3',"金额(元)");
	      $objPHPExcel->getActiveSheet()->setCellValue('H3',"增值税(元)");
	          $h=3;
        for($i=0;$i<count($apply);$i++){
        	$h++;$tmp=$apply[$i];
        	$objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$tmp["em_name"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('B'.$h,$tmp["bencino"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('C'.$h,$tmp["duiyingno"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('D'.$h,$tmp["jiruday"]);
        	if($tmp["type"]==1)
        		$objPHPExcel->getActiveSheet()->setCellValue('E'.$h,"AP");
        	else
        		$objPHPExcel->getActiveSheet()->setCellValue('E'.$h,"TI");
        	$objPHPExcel->getActiveSheet()->setCellValue('F'.$h,$tmp["title"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('G'.$h,$tmp["jine"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('H'.$h,$tmp["zengzhishui"]);
        }

          $hj=$h+1;
         $objPHPExcel->getActiveSheet()->getStyle($hj)->getAlignment()->setWrapText(true);
	    
	     $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':H'.$hj)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	     $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':H'.$hj)->getFill()->getStartColor()->setARGB('00FFCC99');
	         //水平剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':H'.$hj)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':H'.$hj)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

	     $objPHPExcel->getActiveSheet()->setCellValue('A'.$hj,"合计");
	    $objPHPExcel->getActiveSheet()->mergeCells('A'.$hj.':F'.$hj);
	      $objPHPExcel->getActiveSheet()->getStyle('A'.$hj)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
	      $objPHPExcel->getActiveSheet()->setCellValue('G'.$hj, '=SUM(G4:G'.$h.')');
	      $objPHPExcel->getActiveSheet()->setCellValue('H'.$hj, '=SUM(H4:H'.$h.')');
	       $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);



             $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:H'.$hj)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        
          $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=总账对账明细列表.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}
	//暂估明细
	public function zangu($list=20,$page=1){
		$request = Request::instance();$data=$request->post();
		$cainian=Db::table("cg_cainian")->order("starttime")->select();
		$kaohe1=Db::table("cg_kaoheqi1")->order("starttime")->select();
		$this->assign("cainian",$cainian);
		$this->assign("kaohe1",$kaohe1);
		$whereap="ap.is_shengxiao=1 and ap.state in(7,9) and ap.tag=0 and ap.is_actived=1 and ap.is_deleted=0 and apd.is_actived=1 and apd.is_deleted=0 ";
		$whereti="ap.status in(7,9) and ap.status2 is null ";
		if($data){
			$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->where("c.id={$data["cainian"]} and k1.id={$data["kaohe1"]}")
			->field("c.id as cid,k1.id as k1id,c.title as ctitle,k1.title as k1title,k1.starttime as k1start,k1.endtime as k1end")
			->find();
			$ctitle=$nowt["ctitle"];$k1title=$nowt["k1title"];
		}else{
			$nowtime=date("Y-m-d");
			$now=strtotime($nowtime);
			$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			
			->where("k1.starttime<=$now and k1.endtime>=$now")
			->field("c.id as cid,k1.id as k1id,c.title as ctitle,k1.title as k1title,k1.starttime as k1start,k1.endtime as k1end")
			->find();

			$data=["cainian"=>$nowt["cid"],"kaohe1"=>$nowt["k1id"]];
			$ctitle=$nowt["ctitle"];$k1title=$nowt["k1title"];
		}
		$nowt["startdate"]=date("Y-m-d",$nowt["k1start"]);$nowt["enddate"]=date("Y-m-d",$nowt["k1end"]);
		$this->assign("ctitle",$ctitle);$this->assign("k1title",$k1title);
		/*$whereap.="and apd.end_time>='{$nowt["startdate"]}' and apd.start_time<='{$nowt["enddate"]}'";
		$apall=DB::table("ap_apply")->alias("ap")->join("ap_apply_detail apd","apd.apply_id=ap.id")
		->join("users u","ap.c_users_id=u.id")
		->join("employee e","u.employee_id=e.id")
		->join("gys g","apd.gys=g.id")
		->where($whereap)
		->field("ap.id,apd.id,apd.start_time,apd.end_time,apd.pifuzonge,apd.shuilv,g.no as gys_no,e.em_name,ap.no as apno,apd.detail_no as apdno")->select();*/
		//print_r($apall);

		$whereti="ap.status in(7,9) and ap.status2 is null and apd.end>={$nowt["k1start"]} and apd.start<={$nowt["k1end"]}";
		$tiall=Db::table("cg_ti")->alias("ap")->join("cg_ti_list apd","apd.ti_id=ap.id")
		->join("employee e","ap.cuser=e.id")
		->join("cg_jxs j","ap.jxs_id=j.id")
		->where($whereti)->field("ap.id,apd.id,apd.start,apd.end,apd.pizhunhanshui,e.em_name,ap.sn as apno,apd.sn as apdno,j.sn as jno")->select();
		$this->assign("data",$data);
		$all=[];
		/*for($i=0;$i<count($apall);$i++){
			$tmp["gongsi"]="3000";$tmp["gongchang"]="3000";$tmp["zuzhi"]="3010";$tmp["xiaolu"]="00";$tmp["bumen"]="00";
			$tmp["kehu"]=$apall[$i]["gys_no"];$tmp["liaohao"]="";$tmp["em_name"]=$apall[$i]["em_name"];$tmp["no"]=$apall[$i]["apno"]."_".$apall[$i]["apdno"];
			$tmp["type"]="AP";
			$zongts=(strtotime($apall[$i]["end_time"])-strtotime($apall[$i]["start_time"]))/3600/24+1;
			$start=strtotime($apall[$i]["start_time"])<=$nowt["k1start"]?$nowt["k1start"]:strtotime($apall[$i]["start_time"]);
			$end=strtotime($apall[$i]["end_time"])>=$nowt["k1end"]?$nowt["k1end"]:strtotime($apall[$i]["end_time"]);
			$ts=($end-$start)/3600/24+1;
			//echo $apall[$i]["start_time"]."/".$apall[$i]["end_time"]."----";
			//echo $zongts."/".$ts,"<br/>";
			$tmp["feiyong"]=round(($apall[$i]["pifuzonge"]+$apall[$i]["pifuzonge"]*$apall[$i]["shuilv"]/100)/$zongts*$ts,4);
			$tmp["start"]=$apall[$i]["start_time"];$tmp["end"]=$apall[$i]["end_time"];
			$all[]=$tmp;
		}*/
		for($i=0;$i<count($tiall);$i++){
			$tmp["gongsi"]="3000";$tmp["gongchang"]="3000";$tmp["zuzhi"]="3010";$tmp["xiaolu"]="00";$tmp["bumen"]="00";
			$tmp["kehu"]=$tiall[$i]["jno"];$tmp["liaohao"]="";$tmp["em_name"]=$tiall[$i]["em_name"];$tmp["no"]=$tiall[$i]["apno"]."_".$tiall[$i]["apdno"];
			$tmp["type"]="TI";
			$zongts=($tiall[$i]["end"]-$tiall[$i]["start"])/3600/24+1;
			$start=$tiall[$i]["start"]<=$nowt["k1start"]?$nowt["k1start"]:$tiall[$i]["start"];
			$end=$tiall[$i]["end"]>=$nowt["k1end"]?$nowt["k1end"]:$tiall[$i]["end"];
			$ts=($end-$start)/3600/24+1;
			$tmp["feiyong"]=round($tiall[$i]["pizhunhanshui"]/$zongts*$ts,4);
			$tmp["start"]=date("Y-m-d",$tiall[$i]["start"]);$tmp["end"]=date("Y-m-d",$tiall[$i]["end"]);
			$all[]=$tmp;
		}

		$sum=count($all);
		$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $end=$start+$list;
        if($end>=$sum)
        	$end=$sum;
        $detail=[];
          for($i=$start;$i<$end;$i++){
          	$detail[]=$all[$i];
          }
		$this->assign("detail",$detail);
		 $this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		return $this->fetch();
	}
	//导出暂估
	public function daochuzangu($kaohe1){
		$whereap="ap.is_shengxiao=1 and ap.state in(7,9) and ap.tag=0 and ap.is_actived=1 and ap.is_deleted=0 and apd.is_actived=1 and apd.is_deleted=0 ";
		$whereti="ap.status in(7,9) and ap.status2 is null ";
			$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->where("k1.id={$kaohe1}")
			->field("c.id as cid,k1.id as k1id,c.title as ctitle,k1.title as k1title,k1.starttime as k1start,k1.endtime as k1end")
			->find();
			$ctitle=$nowt["ctitle"];$k1title=$nowt["k1title"];
			$nowt["startdate"]=date("Y-m-d",$nowt["k1start"]);$nowt["enddate"]=date("Y-m-d",$nowt["k1end"]);
			$whereap.="and apd.end_time>='{$nowt["startdate"]}' and apd.start_time<='{$nowt["enddate"]}'";
	/*	$apall=DB::table("ap_apply")->alias("ap")->join("ap_apply_detail apd","apd.apply_id=ap.id")
		->join("users u","ap.c_users_id=u.id")
		->join("employee e","u.employee_id=e.id")
		->join("gys g","apd.gys=g.id")
		->where($whereap)
		->field("ap.id,apd.id,apd.start_time,apd.end_time,apd.pifuzonge,apd.shuilv,g.no as gys_no,e.em_name,ap.no as apno,apd.detail_no as apdno")->select();*/
		//print_r($apall);

		$whereti="ap.status in(7,9) and ap.status2 is null and apd.end>={$nowt["k1start"]} and apd.start<={$nowt["k1end"]}";
		$tiall=Db::table("cg_ti")->alias("ap")->join("cg_ti_list apd","apd.ti_id=ap.id")
		->join("employee e","ap.cuser=e.id")
		->join("cg_jxs j","ap.jxs_id=j.id")
		->where($whereti)->field("ap.id,apd.id,apd.start,apd.end,apd.pizhunhanshui,e.em_name,ap.sn as apno,apd.sn as apdno,j.sn as jno")->select();
		
		$all=[];
	/*	for($i=0;$i<count($apall);$i++){
			$tmp["gongsi"]="3000";$tmp["gongchang"]="3000";$tmp["zuzhi"]="3010";$tmp["xiaolu"]="00";$tmp["bumen"]="00";
			$tmp["kehu"]=$apall[$i]["gys_no"];$tmp["liaohao"]="";$tmp["em_name"]=$apall[$i]["em_name"];$tmp["no"]=$apall[$i]["apno"]."_".$apall[$i]["apdno"];
			$tmp["type"]="AP";
			$zongts=(strtotime($apall[$i]["end_time"])-strtotime($apall[$i]["start_time"]))/3600/24+1;
			$start=strtotime($apall[$i]["start_time"])<=$nowt["k1start"]?$nowt["k1start"]:strtotime($apall[$i]["start_time"]);
			$end=strtotime($apall[$i]["end_time"])>=$nowt["k1end"]?$nowt["k1end"]:strtotime($apall[$i]["end_time"]);
			$ts=($end-$start)/3600/24+1;
			//echo $apall[$i]["start_time"]."/".$apall[$i]["end_time"]."----";
			//echo $zongts."/".$ts,"<br/>";
			$tmp["feiyong"]=round(($apall[$i]["pifuzonge"]+$apall[$i]["pifuzonge"]*$apall[$i]["shuilv"]/100)/$zongts*$ts,4);
			$tmp["start"]=$apall[$i]["start_time"];$tmp["end"]=$apall[$i]["end_time"];
			$all[]=$tmp;
		}*/
		for($i=0;$i<count($tiall);$i++){
			$tmp["gongsi"]="3000";$tmp["gongchang"]="3000";$tmp["zuzhi"]="3010";$tmp["xiaolu"]="00";$tmp["bumen"]="00";
			$tmp["kehu"]=$tiall[$i]["jno"];$tmp["liaohao"]="";$tmp["em_name"]=$tiall[$i]["em_name"];$tmp["no"]=$tiall[$i]["apno"]."_".$tiall[$i]["apdno"];
			$tmp["type"]="TI";
			$zongts=($tiall[$i]["end"]-$tiall[$i]["start"])/3600/24+1;
			$start=$tiall[$i]["start"]<=$nowt["k1start"]?$nowt["k1start"]:$tiall[$i]["start"];
			$end=$tiall[$i]["end"]>=$nowt["k1end"]?$nowt["k1end"]:$tiall[$i]["end"];
			$ts=($end-$start)/3600/24+1;
			$tmp["feiyong"]=round($tiall[$i]["pizhunhanshui"]/$zongts*$ts,4);
			$tmp["start"]=date("Y-m-d",$tiall[$i]["start"]);$tmp["end"]=date("Y-m-d",$tiall[$i]["end"]);
			$all[]=$tmp;
		}
		    $eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();
	  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("{$ctitle}-{$k1title}暂估明细表");


          $objPHPExcel->getActiveSheet()->mergeCells('A1:O1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "{$ctitle}-{$k1title}暂估明细表");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:O2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);


         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);$objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
	    
	     $objPHPExcel->getActiveSheet()->getStyle('A3:O3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	     $objPHPExcel->getActiveSheet()->getStyle('A3:O3')->getFill()->getStartColor()->setARGB('0099CCFF');
	         //水平剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A3:O3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A3:O3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	      $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
	      $objPHPExcel->getActiveSheet()->setCellValue('B3',"会计年度期间");
	      $objPHPExcel->getActiveSheet()->setCellValue('C3',"公司代码");
	      $objPHPExcel->getActiveSheet()->setCellValue('D3',"工厂");
	      $objPHPExcel->getActiveSheet()->setCellValue('E3',"销售组织");
	      $objPHPExcel->getActiveSheet()->setCellValue('F3',"配销通路");
	      $objPHPExcel->getActiveSheet()->setCellValue('G3',"部门");
	      $objPHPExcel->getActiveSheet()->setCellValue('H3',"客户");
	      $objPHPExcel->getActiveSheet()->setCellValue('I3',"料号");
	      $objPHPExcel->getActiveSheet()->setCellValue('J3',"业务人员");
	      $objPHPExcel->getActiveSheet()->setCellValue('K3',"专案代码");
	      $objPHPExcel->getActiveSheet()->setCellValue('L3',"费用类别");
	      $objPHPExcel->getActiveSheet()->setCellValue('M3',"销售费用");
	      $objPHPExcel->getActiveSheet()->setCellValue('N3',"促销起始日");
	      $objPHPExcel->getActiveSheet()->setCellValue('O3',"促销截至日");
	          $h=3;
        for($i=0;$i<count($all);$i++){
        	$h++;$tmp=$all[$i];
        	$objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$i+1);
        	$objPHPExcel->getActiveSheet()->setCellValue('B'.$h,"{$ctitle}-{$k1title}");
        	$objPHPExcel->getActiveSheet()->setCellValue('C'.$h,$tmp["gongsi"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('D'.$h,$tmp["gongchang"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('E'.$h,$tmp["zuzhi"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('F'.$h,$tmp["xiaolu"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('G'.$h,$tmp["bumen"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('H'.$h,$tmp["kehu"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('I'.$h,$tmp["liaohao"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('J'.$h,$tmp["em_name"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('K'.$h,$tmp["no"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('L'.$h,$tmp["type"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('M'.$h,$tmp["feiyong"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('N'.$h,$tmp["start"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('O'.$h,$tmp["end"]);
        }
         $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(30);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(30);

             $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:O'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        
          $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header("Content-Disposition:attachment;filename={$ctitle}-{$k1title}暂估明细表.xls");
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}

	//结案明细表
	public function jiean($list=20,$page=1){
		$request = Request::instance();$data=$request->post();
		$cainian=Db::table("cg_cainian")->order("starttime")->select();
		$kaohe1=Db::table("cg_kaoheqi1")->order("starttime")->select();
		$this->assign("cainian",$cainian);
		$this->assign("kaohe1",$kaohe1);
		$whereap="ap.is_shengxiao=1 and ap.state in(7,9) and ap.tag=0 and ap.is_actived=1 and ap.is_deleted=0 and apd.is_actived=1 and apd.is_deleted=0 ";
		$whereti="ap.status in(7,9) and ap.status2 is null ";
		if($data){
			$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->where("c.id={$data["cainian"]} and k1.id={$data["kaohe1"]}")
			->field("c.id as cid,k1.id as k1id,c.title as ctitle,k1.title as k1title,k1.starttime as k1start,k1.endtime as k1end")
			->find();
			$ctitle=$nowt["ctitle"];$k1title=$nowt["k1title"];
		}else{
			$nowtime=date("Y-m-d");
			$now=strtotime($nowtime);
			$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->where("k1.starttime<=$now and k1.endtime>=$now")
			->field("c.id as cid,k1.id as k1id,c.title as ctitle,k1.title as k1title,k1.starttime as k1start,k1.endtime as k1end")
			->find();

			$data=["cainian"=>$nowt["cid"],"kaohe1"=>$nowt["k1id"]];
			$ctitle=$nowt["ctitle"];$k1title=$nowt["k1title"];
		}
		$nowt["startdate"]=date("Y-m-d",$nowt["k1start"]);$nowt["enddate"]=date("Y-m-d",$nowt["k1end"]);
		$this->assign("ctitle",$ctitle);$this->assign("k1title",$k1title);
		

		$whereti="ap.status in(7,9) and ap.status2 is null and apd.end>={$nowt["k1start"]} and apd.start<={$nowt["k1end"]} and ahp.status in (7,9)";
		$tiall=Db::table("cg_ti")->alias("ap")->join("cg_ti_list apd","apd.ti_id=ap.id")
		->join("employee e","ap.cuser=e.id")
		->join("cg_jxs j","ap.jxs_id=j.id")
		->join("cg_ti_hexiao_list ahd","ahd.list_id=apd.id")
		->join("cg_ti_hexiao ahp","ahp.id=ahd.hx_id")
		->where($whereti)->group("apd.id")
		->field("ap.id,apd.id as apdid,apd.start,apd.end,apd.pizhunhanshui,e.em_name,ap.sn as apno,apd.sn as apdno,j.sn as jno,sum(ahd.weishuihexiaojine) as hexiao")->select();
		$this->assign("data",$data);
		//print_r($tiall);
		$all=[];
		
		for($i=0;$i<count($tiall);$i++){
			$tmp["gongsi"]="3000";$tmp["gongchang"]="3000";$tmp["zuzhi"]="3010";$tmp["xiaolu"]="00";$tmp["bumen"]="00";
			$tmp["id"]=$tiall[$i]["apdid"];
			$tmp["kehu"]=$tiall[$i]["jno"];$tmp["liaohao"]="";$tmp["em_name"]=$tiall[$i]["em_name"];$tmp["no"]=$tiall[$i]["apno"]."_".$tiall[$i]["apdno"];
			$tmp["type"]="TI";
			$zongts=($tiall[$i]["end"]-$tiall[$i]["start"])/3600/24+1;
			$start=$tiall[$i]["start"]<=$nowt["k1start"]?$nowt["k1start"]:$tiall[$i]["start"];
			$end=$tiall[$i]["end"]>=$nowt["k1end"]?$nowt["k1end"]:$tiall[$i]["end"];
			$ts=($end-$start)/3600/24+1;
			$tmp["feiyong"]=round($tiall[$i]["pizhunhanshui"]/$zongts*$ts,4);
			$tmp["hexiao"]=round($tiall[$i]["hexiao"]/$zongts*$ts,4);
			$tmp["weihe"]=$tmp["hexiao"]-$tmp["feiyong"];
			$tmp["start"]=date("Y-m-d",$tiall[$i]["start"]);$tmp["end"]=date("Y-m-d",$tiall[$i]["end"]);
			$all[]=$tmp;
		}

		$sum=count($all);
		$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $end=$start+$list;
        if($end>=$sum)
        	$end=$sum;
        $detail=[];
          for($i=$start;$i<$end;$i++){
          	
          	$tmpall=Db::table("cg_ti_hexiao_list")->alias("ahd")
          	->join("cg_ti_hexiao ahp","ahp.id=ahd.hx_id")
          	->where("ahd.list_id={$all[$i]["id"]}  and ahp.status in (7,9)")
          	->order("ahd.id desc")
          	->field("ahp.sn as ahpno,ahd.sn as ahdno")
          	->find();
          	$all[$i]["ahpsn"]=$tmpall["ahpno"]."_".$tmpall["ahdno"];
          	$detail[]=$all[$i];
          }
		$this->assign("detail",$detail);
		 $this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		return $this->fetch();
	}

	//导出结案
	public function daochujiean($kaohe1){
			$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->where("k1.id={$kaohe1}")
			->field("c.id as cid,k1.id as k1id,c.title as ctitle,k1.title as k1title,k1.starttime as k1start,k1.endtime as k1end")
			->find();
			$ctitle=$nowt["ctitle"];$k1title=$nowt["k1title"];
			$nowt["startdate"]=date("Y-m-d",$nowt["k1start"]);$nowt["enddate"]=date("Y-m-d",$nowt["k1end"]);

				$whereti="ap.status in(7,9) and ap.status2 is null and apd.end>={$nowt["k1start"]} and apd.start<={$nowt["k1end"]} and ahp.status in (7,9)";
		$tiall=Db::table("cg_ti")->alias("ap")->join("cg_ti_list apd","apd.ti_id=ap.id")
		->join("employee e","ap.cuser=e.id")
		->join("cg_jxs j","ap.jxs_id=j.id")
		->join("cg_ti_hexiao_list ahd","ahd.list_id=apd.id")
		->join("cg_ti_hexiao ahp","ahp.id=ahd.hx_id")
		->where($whereti)->group("apd.id")
		->field("ap.id,apd.id as apdid,apd.start,apd.end,apd.pizhunhanshui,e.em_name,ap.sn as apno,apd.sn as apdno,j.sn as jno,sum(ahd.weishuihexiaojine) as hexiao")->select();
		
		//print_r($tiall);
		$all=[];
		
		for($i=0;$i<count($tiall);$i++){
			$tmp["gongsi"]="3000";$tmp["gongchang"]="3000";$tmp["zuzhi"]="3010";$tmp["xiaolu"]="00";$tmp["bumen"]="00";
			$tmp["id"]=$tiall[$i]["apdid"];
			$tmp["kehu"]=$tiall[$i]["jno"];$tmp["liaohao"]="";$tmp["em_name"]=$tiall[$i]["em_name"];$tmp["no"]=$tiall[$i]["apno"]."_".$tiall[$i]["apdno"];
			$tmp["type"]="TI";
			$zongts=($tiall[$i]["end"]-$tiall[$i]["start"])/3600/24+1;
			$start=$tiall[$i]["start"]<=$nowt["k1start"]?$nowt["k1start"]:$tiall[$i]["start"];
			$end=$tiall[$i]["end"]>=$nowt["k1end"]?$nowt["k1end"]:$tiall[$i]["end"];
			$ts=($end-$start)/3600/24+1;
			$tmp["feiyong"]=round($tiall[$i]["pizhunhanshui"]/$zongts*$ts,4);
			$tmp["hexiao"]=round($tiall[$i]["hexiao"]/$zongts*$ts,4);
			$tmp["weihe"]=$tmp["hexiao"]-$tmp["feiyong"];
			$tmp["start"]=date("Y-m-d",$tiall[$i]["start"]);$tmp["end"]=date("Y-m-d",$tiall[$i]["end"]);
			$all[]=$tmp;
		}

	
        $detail=[];
          for($i=0;$i<count($tiall);$i++){
          	
          	$tmpall=Db::table("cg_ti_hexiao_list")->alias("ahd")
          	->join("cg_ti_hexiao ahp","ahp.id=ahd.hx_id")
          	->where("ahd.list_id={$all[$i]["id"]}  and ahp.status in (7,9)")
          	->order("ahd.id desc")
          	->field("ahp.sn as ahpno,ahd.sn as ahdno")
          	->find();
          	$all[$i]["ahpsn"]=$tmpall["ahpno"]."_".$tmpall["ahdno"];
          	$detail[]=$all[$i];
          }
           $eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();
	  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("{$ctitle}-{$k1title}结案明细表");


          $objPHPExcel->getActiveSheet()->mergeCells('A1:R1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "{$ctitle}-{$k1title}结案明细表");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:R2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);


         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('A3:R3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	     $objPHPExcel->getActiveSheet()->getStyle('A3:R3')->getFill()->getStartColor()->setARGB('0099CCFF');
	         //水平剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A3:R3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A3:R3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	      $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
	      $objPHPExcel->getActiveSheet()->setCellValue('B3',"会计年度期间");
	      $objPHPExcel->getActiveSheet()->setCellValue('C3',"公司代码");
	      $objPHPExcel->getActiveSheet()->setCellValue('D3',"工厂");
	      $objPHPExcel->getActiveSheet()->setCellValue('E3',"销售组织");
	      $objPHPExcel->getActiveSheet()->setCellValue('F3',"配销通路");
	      $objPHPExcel->getActiveSheet()->setCellValue('G3',"部门");
	      $objPHPExcel->getActiveSheet()->setCellValue('H3',"客户");
	      $objPHPExcel->getActiveSheet()->setCellValue('I3',"料号");
	      $objPHPExcel->getActiveSheet()->setCellValue('J3',"业务人员");
	      $objPHPExcel->getActiveSheet()->setCellValue('K3',"专案代码");
	      $objPHPExcel->getActiveSheet()->setCellValue('L3',"结案流水号");
	      $objPHPExcel->getActiveSheet()->setCellValue('M3',"费用类别");
	      $objPHPExcel->getActiveSheet()->setCellValue('N3',"暂估值-销售费用");
	      $objPHPExcel->getActiveSheet()->setCellValue('O3',"结案值-销售费用");
	      $objPHPExcel->getActiveSheet()->setCellValue('P3',"销售费用");
	      $objPHPExcel->getActiveSheet()->setCellValue('Q3',"促销起始日");
	      $objPHPExcel->getActiveSheet()->setCellValue('R3',"促销截至日");
	           $h=3;
        for($i=0;$i<count($detail);$i++){
        	$h++;$tmp=$detail[$i];
        	$objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$i+1);
        	$objPHPExcel->getActiveSheet()->setCellValue('B'.$h,"{$ctitle}-{$k1title}");
        	$objPHPExcel->getActiveSheet()->setCellValue('C'.$h,$tmp["gongsi"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('D'.$h,$tmp["gongchang"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('E'.$h,$tmp["zuzhi"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('F'.$h,$tmp["xiaolu"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('G'.$h,$tmp["bumen"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('H'.$h,$tmp["kehu"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('I'.$h,$tmp["liaohao"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('J'.$h,$tmp["em_name"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('K'.$h,$tmp["no"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('L'.$h,$tmp["ahpsn"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('M'.$h,$tmp["type"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('N'.$h,$tmp["feiyong"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('O'.$h,$tmp["hexiao"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('P'.$h,$tmp["weihe"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('Q'.$h,$tmp["start"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('R'.$h,$tmp["end"]);
        }
         $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(30);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(30);
             $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(30);
              $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(30);
               $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(30);

             $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:R'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        
          $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header("Content-Disposition:attachment;filename={$ctitle}-{$k1title}结案明细表.xls");
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}

	//补货明细
	public function buhuo($page=1,$list=20){
		$request = Request::instance();$data=$request->post();
		$cainian=Db::table("cg_cainian")->order("starttime")->select();
		$kaohe1=Db::table("cg_kaoheqi1")->order("starttime")->select();
		$this->assign("cainian",$cainian);
		$this->assign("kaohe1",$kaohe1);
		if($data){
			$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->where("c.id={$data["cainian"]} and k1.id={$data["kaohe1"]}")
			->field("c.id as cid,k1.id as k1id,c.title as ctitle,k1.title as k1title,k1.starttime as k1start,k1.endtime as k1end")
			->find();
			$ctitle=$nowt["ctitle"];$k1title=$nowt["k1title"];
		}else{
			$nowtime=date("Y-m-d");
			$now=strtotime($nowtime);
			$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			
			->where("k1.starttime<=$now and k1.endtime>=$now")
			->field("c.id as cid,k1.id as k1id,c.title as ctitle,k1.title as k1title,k1.starttime as k1start,k1.endtime as k1end")
			->find();

			$data=["cainian"=>$nowt["cid"],"kaohe1"=>$nowt["k1id"]];
			$ctitle=$nowt["ctitle"];$k1title=$nowt["k1title"];
		}
		$endday=$nowt["k1end"]+3600*24;
		$nowt["startdate"]=date("Y-m-d",$nowt["k1start"]);$nowt["enddate"]=date("Y-m-d",$nowt["k1end"]);
		$this->assign("ctitle",$ctitle);$this->assign("k1title",$k1title);
		$whereti="o.status in(1,7,9) and o.sqrq>={$nowt["k1start"]} and o.sqrq<{$endday}";
		$sum=Db::table("cg_order_huobu")->alias("hb")
		->join("cg_ti_hexiao_list ahd","hb.hxdmxbh=ahd.id")
		->join("cg_ti_hexiao ahp","ahd.hx_id=ahp.id")
		->join("cg_order o","o.id=hb.order_id")
		->join("cg_ti_list apd","ahd.list_id=apd.id")
		->join("cg_jxs j","apd.jxs_id=j.id","LEFT")
		->join("cg_ti ap","apd.ti_id=ap.id")
		->join("employee e","o.sqr=e.id")
		->where($whereti)->count();

		$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

		$apply=Db::table("cg_order_huobu")->alias("hb")
		->join("cg_ti_hexiao_list ahd","hb.hxdmxbh=ahd.id")
		->join("cg_ti_hexiao ahp","ahd.hx_id=ahp.id")
		->join("cg_order o","o.id=hb.order_id")
		->join("cg_ti_list apd","ahd.list_id=apd.id")
		->join("cg_jxs j","apd.jxs_id=j.id","LEFT")
		->join("cg_ti ap","apd.ti_id=ap.id")
		->join("employee e","o.sqr=e.id")
		->where($whereti)->order("hb.id,o.id")->limit($start,$list)
		->field("j.sn as jxs_no,j.title as jxs_title,ap.sn as ap_no,o.sn as o_no,e.em_name,apd.sn as apd_no,ahp.sn as ahp_no,ahd.sn as ahd_no,apd.start,apd.end,hb.prod_jine")
		->select();
		for($i=0;$i<count($apply);$i++){
			$apply[$i]["gongsi"]="3000";$apply[$i]["gongchang"]="3000";$apply[$i]["zuzhi"]="3010";$apply[$i]["xiaolu"]="00";$apply[$i]["bumen"]="00";$apply[$i]["type"]="T";
			$apply[$i]["start"]=date("Y-m-d",$apply[$i]["start"]);$apply[$i]["end"]=date("Y-m-d",$apply[$i]["end"]);
		}
		//print_r($apply);
		$this->assign("apply",$apply);
		$this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		$this->assign("data",$data);
		return $this->fetch();
	}
	//导出补货
	public function daochubuhuo($kaohe1){
		$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->where("k1.id={$kaohe1}")
			->field("c.id as cid,k1.id as k1id,c.title as ctitle,k1.title as k1title,k1.starttime as k1start,k1.endtime as k1end")
			->find();
			$ctitle=$nowt["ctitle"];$k1title=$nowt["k1title"];
			$nowt["startdate"]=date("Y-m-d",$nowt["k1start"]);$nowt["enddate"]=date("Y-m-d",$nowt["k1end"]);$endday=$nowt["k1end"]+3600*24;
			$whereti="o.status in(1,7,9) and o.sqrq>={$nowt["k1start"]} and o.sqrq<{$endday}";

		$apply=Db::table("cg_order_huobu")->alias("hb")
		->join("cg_ti_hexiao_list ahd","hb.hxdmxbh=ahd.id")
		->join("cg_ti_hexiao ahp","ahd.hx_id=ahp.id")
		->join("cg_order o","o.id=hb.order_id")
		->join("cg_ti_list apd","ahd.list_id=apd.id")
		->join("cg_jxs j","apd.jxs_id=j.id","LEFT")
		->join("cg_ti ap","apd.ti_id=ap.id")
		->join("employee e","o.sqr=e.id")
		->where($whereti)->order("hb.id,o.id")
		->field("j.sn as jxs_no,j.title as jxs_title,ap.sn as ap_no,o.sn as o_no,e.em_name,apd.sn as apd_no,ahp.sn as ahp_no,ahd.sn as ahd_no,apd.start,apd.end,hb.prod_jine")
		->select();
		for($i=0;$i<count($apply);$i++){
			$apply[$i]["gongsi"]="3000";$apply[$i]["gongchang"]="3000";$apply[$i]["zuzhi"]="3010";$apply[$i]["xiaolu"]="00";$apply[$i]["bumen"]="00";$apply[$i]["type"]="T";
			$apply[$i]["start"]=date("Y-m-d",$apply[$i]["start"]);$apply[$i]["end"]=date("Y-m-d",$apply[$i]["end"]);
		}
			    $eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();
	  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("{$ctitle}-{$k1title}补货明细表");


          $objPHPExcel->getActiveSheet()->mergeCells('A1:P1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "{$ctitle}-{$k1title}补货明细表");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:P2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);

         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);$objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
	    
	     $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	     $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getFill()->getStartColor()->setARGB('0099CCFF');
	         //水平剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	      $objPHPExcel->getActiveSheet()->setCellValue('A3',"会计年度期间");
	      $objPHPExcel->getActiveSheet()->setCellValue('B3',"公司代码");
	      $objPHPExcel->getActiveSheet()->setCellValue('C3',"工厂");
	      $objPHPExcel->getActiveSheet()->setCellValue('D3',"销售组织");
	      $objPHPExcel->getActiveSheet()->setCellValue('E3',"配销通路");
	      $objPHPExcel->getActiveSheet()->setCellValue('F3',"部门");
	      $objPHPExcel->getActiveSheet()->setCellValue('G3',"客户");
	      $objPHPExcel->getActiveSheet()->setCellValue('H3',"促销案料号");
	      $objPHPExcel->getActiveSheet()->setCellValue('I3',"补货料号");
	      $objPHPExcel->getActiveSheet()->setCellValue('J3',"业务人员");
	      $objPHPExcel->getActiveSheet()->setCellValue('K3',"专案代码");
	      $objPHPExcel->getActiveSheet()->setCellValue('L3',"结案流水号");
	      $objPHPExcel->getActiveSheet()->setCellValue('M3',"费用类别");
	      $objPHPExcel->getActiveSheet()->setCellValue('N3',"补货数量");
	      $objPHPExcel->getActiveSheet()->setCellValue('O3',"促销起始日");
	      $objPHPExcel->getActiveSheet()->setCellValue('P3',"促销截至日");
	          $h=3;
        for($i=0;$i<count($apply);$i++){
        	$h++;$tmp=$apply[$i];
        	$objPHPExcel->getActiveSheet()->setCellValue('A'.$h,"{$ctitle}-{$k1title}");
        	$objPHPExcel->getActiveSheet()->setCellValue('B'.$h,$tmp["gongsi"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('C'.$h,$tmp["gongchang"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('D'.$h,$tmp["zuzhi"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('E'.$h,$tmp["xiaolu"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('F'.$h,$tmp["bumen"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('G'.$h,$tmp["jxs_no"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('H'.$h,$tmp["ap_no"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('I'.$h,$tmp["o_no"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('J'.$h,$tmp["em_name"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('K'.$h,"{$tmp["ap_no"]}_{$tmp["apd_no"]}");
        	$objPHPExcel->getActiveSheet()->setCellValue('L'.$h,"{$tmp["ahp_no"]}_{$tmp["ahd_no"]}");
        	$objPHPExcel->getActiveSheet()->setCellValue('M'.$h,$tmp["type"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('N'.$h,$tmp["prod_jine"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('O'.$h,$tmp["start"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('P'.$h,$tmp["end"]);
        }
         $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(30);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(30);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(30);
             $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:P'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        
          $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header("Content-Disposition:attachment;filename={$ctitle}-{$k1title}补货明细表.xls");
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}
	//余额表
	public function yue(){
		$request = Request::instance();$data=$request->post();
		$cainian=Db::table("cg_cainian")->order("starttime")->select();
		$kaohe1=Db::table("cg_kaoheqi1")->order("starttime")->select();
		$this->assign("cainian",$cainian);
		$this->assign("kaohe1",$kaohe1);
		if($data){
			$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->where("c.id={$data["cainian"]} and k1.id={$data["kaohe1"]}")
			->field("c.id as cid,k1.id as k1id,c.title as ctitle,k1.title as k1title,k1.starttime as k1start,k1.endtime as k1end")
			->find();
			$ctitle=$nowt["ctitle"];$k1title=$nowt["k1title"];
		}else{
			$nowtime=date("Y-m-d");
			$now=strtotime($nowtime);
			$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			
			->where("k1.starttime<=$now and k1.endtime>=$now")
			->field("c.id as cid,k1.id as k1id,c.title as ctitle,k1.title as k1title,k1.starttime as k1start,k1.endtime as k1end")
			->find();

			$data=["cainian"=>$nowt["cid"],"kaohe1"=>$nowt["k1id"]];
			$ctitle=$nowt["ctitle"];$k1title=$nowt["k1title"];
		}
		$endday=$nowt["k1end"]+3600*24;
		$nowt["startdate"]=date("Y-m-d",$nowt["k1start"]);$nowt["enddate"]=date("Y-m-d",$nowt["k1end"]);
		$this->assign("ctitle",$ctitle);$this->assign("k1title",$k1title);

		$hebuhuo=DB::table("cg_ti_hexiao_list")->alias("ahd")
		->join("cg_ti_hexiao ahp","ahd.hx_id=ahp.id")
		->join("cg_ti_list apd","ahd.list_id=apd.id")
		->join("cg_ti ap","apd.ti_id=ap.id")
		->join("apti_zhifu_list zf","zf.hx_id=ahd.id","LEFT")
		->group("ahd.id")
		->where("ap.ctime>={$nowt["k1start"]} and ap.ctime<{$endday} and ahp.status in (7,9)")
		->field("ahd.hanshuihexiaojine,sum(zf.bcjine) as zhifu,ahd.id,ahd.zhifufangshi")
		->select();
		//print_r($hebuhuo);
		$hebuhuonum=0;$hebufeinum=0;$hezongnum=0;
		for($i=0;$i<count($hebuhuo);$i++){
			if($hebuhuo[$i]["zhifufangshi"]==13)
				$hebuhuonum=$hebuhuonum+$hebuhuo[$i]["hanshuihexiaojine"]-$hebuhuo[$i]["zhifu"];
			else
				$hebufeinum=$hebufeinum+$hebuhuo[$i]["hanshuihexiaojine"]-$hebuhuo[$i]["zhifu"];
			$hezongnum=$hezongnum+$hebuhuo[$i]["hanshuihexiaojine"]-$hebuhuo[$i]["zhifu"];
		}
		$hebuhuonum=round($hebuhuonum,2);
		$hebufeinum=round($hebufeinum,2);
		$hezongnum=round($hebufeinum,2);
		$this->assign("hebuhuonum",$hebuhuonum);
		$this->assign("hebufeinum",$hebufeinum);
		$this->assign("hezongnum",$hezongnum);

		$weihe=Db::table("cg_ti_list")->alias("apd")
		->join("cg_ti ap","apd.ti_id=ap.id")
		->where("ap.ctime>={$nowt["k1start"]} and ap.ctime<{$endday} and ap.status in (7,9) and ap.status2 is null and apd.hx_status<>2")
		->field("apd.pizhunhanshui,apd.paymethod")
		->select();

		$weihebuhuo=0;$weihebufei=0;$weihezong=0;
		for($i=0;$i<count($weihe);$i++){
			if($weihe[$i]["paymethod"]==13)
				$weihebuhuo+=$weihe[$i]["pizhunhanshui"];
			else
				$weihebufei+=$weihe[$i]["pizhunhanshui"];
			$weihezong+=$weihe[$i]["pizhunhanshui"];
		}
		$weihebuhuo=round($weihebuhuo,2);
		$weihebufei=round($weihebufei,2);
		$weihezong=round($weihezong,2);
		$this->assign("weihebuhuo",$weihebuhuo);$this->assign("weihebufei",$weihebufei);$this->assign("weihezong",$weihezong);


		$allbuhuo=$weihebuhuo+$hebuhuonum;
		$this->assign("allbuhuo",$allbuhuo);
		$allbufei=$weihebufei+$hebufeinum;
		$this->assign("allbufei",$allbufei);
		$allzong=$weihezong+$hezongnum;
		$this->assign("allzong",$allzong);
		$this->assign("data",$data);
		return $this->fetch();
	}
	//导出余额表
	public function daochuyue($kaohe1){
		$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->where("k1.id={$kaohe1}")
			->field("c.id as cid,k1.id as k1id,c.title as ctitle,k1.title as k1title,k1.starttime as k1start,k1.endtime as k1end")
			->find();
			$ctitle=$nowt["ctitle"];$k1title=$nowt["k1title"];
			$nowt["startdate"]=date("Y-m-d",$nowt["k1start"]);$nowt["enddate"]=date("Y-m-d",$nowt["k1end"]);$endday=$nowt["k1end"]+3600*24;

		$hebuhuo=DB::table("cg_ti_hexiao_list")->alias("ahd")
		->join("cg_ti_hexiao ahp","ahd.hx_id=ahp.id")
		->join("cg_ti_list apd","ahd.list_id=apd.id")
		->join("cg_ti ap","apd.ti_id=ap.id")
		->join("apti_zhifu_list zf","zf.hx_id=ahd.id","LEFT")
		->group("ahd.id")
		->where("ap.ctime>={$nowt["k1start"]} and ap.ctime<{$endday} and ahp.status in (7,9)")
		->field("ahd.hanshuihexiaojine,sum(zf.bcjine) as zhifu,ahd.id,ahd.zhifufangshi")
		->select();
		//print_r($hebuhuo);
		$hebuhuonum=0;$hebufeinum=0;$hezongnum=0;
		for($i=0;$i<count($hebuhuo);$i++){
			if($hebuhuo[$i]["zhifufangshi"]==13)
				$hebuhuonum=$hebuhuonum+$hebuhuo[$i]["hanshuihexiaojine"]-$hebuhuo[$i]["zhifu"];
			else
				$hebufeinum=$hebufeinum+$hebuhuo[$i]["hanshuihexiaojine"]-$hebuhuo[$i]["zhifu"];
			$hezongnum=$hezongnum+$hebuhuo[$i]["hanshuihexiaojine"]-$hebuhuo[$i]["zhifu"];
		}
		$hebuhuonum=round($hebuhuonum,2);
		$hebufeinum=round($hebufeinum,2);
		$hezongnum=round($hebufeinum,2);
		

		$weihe=Db::table("cg_ti_list")->alias("apd")
		->join("cg_ti ap","apd.ti_id=ap.id")
		->where("ap.ctime>={$nowt["k1start"]} and ap.ctime<{$endday} and ap.status in (7,9) and ap.status2 is null and apd.hx_status<>2")
		->field("apd.pizhunhanshui,apd.paymethod")
		->select();

		$weihebuhuo=0;$weihebufei=0;$weihezong=0;
		for($i=0;$i<count($weihe);$i++){
			if($weihe[$i]["paymethod"]==13)
				$weihebuhuo+=$weihe[$i]["pizhunhanshui"];
			else
				$weihebufei+=$weihe[$i]["pizhunhanshui"];
			$weihezong+=$weihe[$i]["pizhunhanshui"];
		}
		$weihebuhuo=round($weihebuhuo,2);
		$weihebufei=round($weihebufei,2);
		$weihezong=round($weihezong,2);
	

		$allbuhuo=$weihebuhuo+$hebuhuonum;
	
		$allbufei=$weihebufei+$hebufeinum;
		
		$allzong=$weihezong+$hezongnum;


		    $eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();
	  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("{$ctitle}-{$k1title}余额表");


          $objPHPExcel->getActiveSheet()->mergeCells('A1:D1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "{$ctitle}-{$k1title}余额表");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:D2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);

          $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
	    
	     $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	     $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getFill()->getStartColor()->setARGB('0099CCFF');
	         //水平剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	      $objPHPExcel->getActiveSheet()->setCellValue('A3',"项目");
	      $objPHPExcel->getActiveSheet()->setCellValue('B3',"核清价补货(元)");
	      $objPHPExcel->getActiveSheet()->setCellValue('C3',"补费(元)");
	      $objPHPExcel->getActiveSheet()->setCellValue('D3',"核清价补货补费合计(元)");


	       $objPHPExcel->getActiveSheet()->setCellValue('A4',"已结案完成的未补费用");
	       $objPHPExcel->getActiveSheet()->setCellValue('A5',"执行时间段申请案费用");
	       $objPHPExcel->getActiveSheet()->setCellValue('A6',"余额合计");

	        $objPHPExcel->getActiveSheet()->setCellValue('B4',$hebuhuonum);
	        $objPHPExcel->getActiveSheet()->setCellValue('C4',$hebufeinum);
	        $objPHPExcel->getActiveSheet()->setCellValue('D4',$hezongnum);
	         $objPHPExcel->getActiveSheet()->setCellValue('B5',$weihebuhuo);
	        $objPHPExcel->getActiveSheet()->setCellValue('C5',$weihebufei);
	        $objPHPExcel->getActiveSheet()->setCellValue('D5',$weihezong);
	          $objPHPExcel->getActiveSheet()->setCellValue('B6',$allbuhuo);
	        $objPHPExcel->getActiveSheet()->setCellValue('C6',$allbufei);
	        $objPHPExcel->getActiveSheet()->setCellValue('D6',$allzong);


	          $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);


              $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:D6')->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        
          $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header("Content-Disposition:attachment;filename={$ctitle}-{$k1title}余额表.xls");
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}	

	//汇总
	public function huizong(){
			$request = Request::instance();$data=$request->post();
		$cainian=Db::table("cg_cainian")->order("starttime")->select();
		$kaohe1=Db::table("cg_kaoheqi1")->order("starttime")->select();
		$this->assign("cainian",$cainian);
		$this->assign("kaohe1",$kaohe1);
		if($data){
			$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->where("c.id={$data["cainian"]} and k1.id={$data["kaohe1"]}")
			->field("c.id as cid,k1.id as k1id,c.title as ctitle,k1.title as k1title,k1.starttime as k1start,k1.endtime as k1end")
			->find();
			$ctitle=$nowt["ctitle"];$k1title=$nowt["k1title"];
		}else{
			$nowtime=date("Y-m-d");
			$now=strtotime($nowtime);
			$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			
			->where("k1.starttime<=$now and k1.endtime>=$now")
			->field("c.id as cid,k1.id as k1id,c.title as ctitle,k1.title as k1title,k1.starttime as k1start,k1.endtime as k1end")
			->find();

			$data=["cainian"=>$nowt["cid"],"kaohe1"=>$nowt["k1id"]];
			$ctitle=$nowt["ctitle"];$k1title=$nowt["k1title"];
		}
		$endday=$nowt["k1end"]+3600*24;
		$nowt["startdate"]=date("Y-m-d",$nowt["k1start"]);$nowt["enddate"]=date("Y-m-d",$nowt["k1end"]);
		$this->assign("ctitle",$ctitle);$this->assign("k1title",$k1title);


		$this->assign("data",$data);
		//求暂估
		$wheretizan="ap.status in(7,9) and ap.status2 is null and apd.end>={$nowt["k1start"]} and apd.start<={$nowt["k1end"]}";
		$tiall=Db::table("cg_ti")->alias("ap")->join("cg_ti_list apd","apd.ti_id=ap.id")
		->join("employee e","ap.cuser=e.id")
		->join("cg_jxs j","ap.jxs_id=j.id")
		->where($wheretizan)->field("ap.id,apd.id,apd.start,apd.end,apd.pizhunhanshui,e.em_name,ap.sn as apno,apd.sn as apdno,j.sn as jno,apd.paymethod")->select();
		$zanguzong=0;$buzangu=0;
		for($i=0;$i<count($tiall);$i++){
		
			$zongts=($tiall[$i]["end"]-$tiall[$i]["start"])/3600/24+1;
			$start=$tiall[$i]["start"]<=$nowt["k1start"]?$nowt["k1start"]:$tiall[$i]["start"];
			$end=$tiall[$i]["end"]>=$nowt["k1end"]?$nowt["k1end"]:$tiall[$i]["end"];
			$ts=($end-$start)/3600/24+1;
			$tmp["feiyong"]=round($tiall[$i]["pizhunhanshui"]/$zongts*$ts,4);
			
			
			
			$zanguzong+=$tmp["feiyong"];
		}
		$zanguzong=round($zanguzong,2);
		$this->assign("zanguzong",$zanguzong);$this->assign("buzangu",$buzangu);
		//求结案
			$whereti="ap.status in(7,9) and ap.status2 is null and apd.end>={$nowt["k1start"]} and apd.start<={$nowt["k1end"]} and ahp.status in (7,9)";
		$tiall=Db::table("cg_ti")->alias("ap")->join("cg_ti_list apd","apd.ti_id=ap.id")
		->join("employee e","ap.cuser=e.id")
		->join("cg_jxs j","ap.jxs_id=j.id")
		->join("cg_ti_hexiao_list ahd","ahd.list_id=apd.id")
		->join("cg_ti_hexiao ahp","ahp.id=ahd.hx_id")
		->where($whereti)->group("apd.id")
		->field("ap.id,apd.id as apdid,apd.start,apd.end,apd.pizhunhanshui,e.em_name,ap.sn as apno,apd.sn as apdno,j.sn as jno,sum(ahd.weishuihexiaojine) as hexiao")->select();
		
		$jieanzong=0;
		for($i=0;$i<count($tiall);$i++){
			
			$zongts=($tiall[$i]["end"]-$tiall[$i]["start"])/3600/24+1;
			$start=$tiall[$i]["start"]<=$nowt["k1start"]?$nowt["k1start"]:$tiall[$i]["start"];
			$end=$tiall[$i]["end"]>=$nowt["k1end"]?$nowt["k1end"]:$tiall[$i]["end"];
			$ts=($end-$start)/3600/24+1;
			$tmp["feiyong"]=round($tiall[$i]["pizhunhanshui"]/$zongts*$ts,4);
			$tmp["hexiao"]=round($tiall[$i]["hexiao"]/$zongts*$ts,4);
			$tmp["weihe"]=$tmp["hexiao"]-$tmp["feiyong"];
			$jieanzong+=$tmp["weihe"];
		}
		$jieanzong=round($jieanzong,2);
		$this->assign("jieanzong",$jieanzong);
		//求补货
			$whereti="ap.status in(7,9) and ap.status2 is null and apd.end>={$nowt["k1start"]} and apd.start<={$nowt["k1end"]} and ahp.status in (7,9) and ahd.zhifufangshi=13";
		$tiall=Db::table("cg_ti")->alias("ap")->join("cg_ti_list apd","apd.ti_id=ap.id")
		->join("employee e","ap.cuser=e.id")
		->join("cg_jxs j","ap.jxs_id=j.id")
		->join("cg_ti_hexiao_list ahd","ahd.list_id=apd.id")
		->join("cg_ti_hexiao ahp","ahp.id=ahd.hx_id")
		->where($whereti)->group("apd.id")
		->field("ap.id,apd.id as apdid,apd.start,apd.end,apd.pizhunhanshui,e.em_name,ap.sn as apno,apd.sn as apdno,j.sn as jno,sum(ahd.weishuihexiaojine) as hexiao")->select();
		for($i=0;$i<count($tiall);$i++){
			
			$zongts=($tiall[$i]["end"]-$tiall[$i]["start"])/3600/24+1;
			$start=$tiall[$i]["start"]<=$nowt["k1start"]?$nowt["k1start"]:$tiall[$i]["start"];
			$end=$tiall[$i]["end"]>=$nowt["k1end"]?$nowt["k1end"]:$tiall[$i]["end"];
			$ts=($end-$start)/3600/24+1;
			$tmp["feiyong"]=round($tiall[$i]["pizhunhanshui"]/$zongts*$ts,4);
			$tmp["hexiao"]=round($tiall[$i]["hexiao"]/$zongts*$ts,4);
		
			$buzangu+=$tmp["hexiao"];
		}

		$buzangu=round($buzangu,2);
		$this->assign("buzangu",$buzangu);

		$whereti="o.status in(1,7,9) and apd.end>={$nowt["k1start"]} and apd.start<={$nowt["k1end"]}";
		$tiall=Db::table("cg_order_huobu")->alias("hb")
		->join("cg_ti_hexiao_list ahd","hb.hxdmxbh=ahd.id")
		->join("cg_ti_hexiao ahp","ahd.hx_id=ahp.id")
		->join("cg_order o","o.id=hb.order_id")
		->join("cg_ti_list apd","ahd.list_id=apd.id")
		->join("cg_jxs j","apd.jxs_id=j.id","LEFT")
		->join("cg_ti ap","apd.ti_id=ap.id")
		->join("employee e","o.sqr=e.id")
		->where($whereti)->order("hb.id,o.id")
		->field("j.sn as jxs_no,j.title as jxs_title,ap.sn as ap_no,o.sn as o_no,e.em_name,apd.sn as apd_no,ahp.sn as ahp_no,ahd.sn as ahd_no,apd.start,apd.end,hb.prod_jine")
		->select();
		//print_r($tiall);
		$buhuo=0;
		for($i=0;$i<count($tiall);$i++){
			
			$zongts=($tiall[$i]["end"]-$tiall[$i]["start"])/3600/24+1;
			$start=$tiall[$i]["start"]<=$nowt["k1start"]?$nowt["k1start"]:$tiall[$i]["start"];
			$end=$tiall[$i]["end"]>=$nowt["k1end"]?$nowt["k1end"]:$tiall[$i]["end"];
			$ts=($end-$start)/3600/24+1;
			$tmp["feiyong"]=round($tiall[$i]["prod_jine"]/$zongts*$ts,4);
		
			$buhuo-=$tmp["feiyong"];
		}
		$buhuo=round($buhuo,2);
		$this->assign("buhuo",$buhuo);
		$zongji=$zanguzong+$jieanzong+$buzangu+$buhuo;
		$this->assign("zongji",$zongji);
		return $this->fetch();
	}
	//导出汇总
	public function daochuhuizong($kaohe1){
		$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->where("k1.id={$kaohe1}")
			->field("c.id as cid,k1.id as k1id,c.title as ctitle,k1.title as k1title,k1.starttime as k1start,k1.endtime as k1end")
			->find();
			$ctitle=$nowt["ctitle"];$k1title=$nowt["k1title"];
			$nowt["startdate"]=date("Y-m-d",$nowt["k1start"]);$nowt["enddate"]=date("Y-m-d",$nowt["k1end"]);$endday=$nowt["k1end"]+3600*24;
				//求暂估
		$wheretizan="ap.status in(7,9) and ap.status2 is null and apd.end>={$nowt["k1start"]} and apd.start<={$nowt["k1end"]}";
		$tiall=Db::table("cg_ti")->alias("ap")->join("cg_ti_list apd","apd.ti_id=ap.id")
		->join("employee e","ap.cuser=e.id")
		->join("cg_jxs j","ap.jxs_id=j.id")
		->where($wheretizan)->field("ap.id,apd.id,apd.start,apd.end,apd.pizhunhanshui,e.em_name,ap.sn as apno,apd.sn as apdno,j.sn as jno,apd.paymethod")->select();
		$zanguzong=0;$buzangu=0;
		for($i=0;$i<count($tiall);$i++){
		
			$zongts=($tiall[$i]["end"]-$tiall[$i]["start"])/3600/24+1;
			$start=$tiall[$i]["start"]<=$nowt["k1start"]?$nowt["k1start"]:$tiall[$i]["start"];
			$end=$tiall[$i]["end"]>=$nowt["k1end"]?$nowt["k1end"]:$tiall[$i]["end"];
			$ts=($end-$start)/3600/24+1;
			$tmp["feiyong"]=round($tiall[$i]["pizhunhanshui"]/$zongts*$ts,4);
			
			
			
			$zanguzong+=$tmp["feiyong"];
		}
		$zanguzong=round($zanguzong,2);
		
		//求结案
			$whereti="ap.status in(7,9) and ap.status2 is null and apd.end>={$nowt["k1start"]} and apd.start<={$nowt["k1end"]} and ahp.status in (7,9)";
		$tiall=Db::table("cg_ti")->alias("ap")->join("cg_ti_list apd","apd.ti_id=ap.id")
		->join("employee e","ap.cuser=e.id")
		->join("cg_jxs j","ap.jxs_id=j.id")
		->join("cg_ti_hexiao_list ahd","ahd.list_id=apd.id")
		->join("cg_ti_hexiao ahp","ahp.id=ahd.hx_id")
		->where($whereti)->group("apd.id")
		->field("ap.id,apd.id as apdid,apd.start,apd.end,apd.pizhunhanshui,e.em_name,ap.sn as apno,apd.sn as apdno,j.sn as jno,sum(ahd.weishuihexiaojine) as hexiao")->select();
		
		$jieanzong=0;
		for($i=0;$i<count($tiall);$i++){
			
			$zongts=($tiall[$i]["end"]-$tiall[$i]["start"])/3600/24+1;
			$start=$tiall[$i]["start"]<=$nowt["k1start"]?$nowt["k1start"]:$tiall[$i]["start"];
			$end=$tiall[$i]["end"]>=$nowt["k1end"]?$nowt["k1end"]:$tiall[$i]["end"];
			$ts=($end-$start)/3600/24+1;
			$tmp["feiyong"]=round($tiall[$i]["pizhunhanshui"]/$zongts*$ts,4);
			$tmp["hexiao"]=round($tiall[$i]["hexiao"]/$zongts*$ts,4);
			$tmp["weihe"]=$tmp["hexiao"]-$tmp["feiyong"];
			$jieanzong+=$tmp["weihe"];
		}
		$jieanzong=round($jieanzong,2);
		
		//求补货
			$whereti="ap.status in(7,9) and ap.status2 is null and apd.end>={$nowt["k1start"]} and apd.start<={$nowt["k1end"]} and ahp.status in (7,9) and ahd.zhifufangshi=13";
		$tiall=Db::table("cg_ti")->alias("ap")->join("cg_ti_list apd","apd.ti_id=ap.id")
		->join("employee e","ap.cuser=e.id")
		->join("cg_jxs j","ap.jxs_id=j.id")
		->join("cg_ti_hexiao_list ahd","ahd.list_id=apd.id")
		->join("cg_ti_hexiao ahp","ahp.id=ahd.hx_id")
		->where($whereti)->group("apd.id")
		->field("ap.id,apd.id as apdid,apd.start,apd.end,apd.pizhunhanshui,e.em_name,ap.sn as apno,apd.sn as apdno,j.sn as jno,sum(ahd.weishuihexiaojine) as hexiao")->select();
		for($i=0;$i<count($tiall);$i++){
			
			$zongts=($tiall[$i]["end"]-$tiall[$i]["start"])/3600/24+1;
			$start=$tiall[$i]["start"]<=$nowt["k1start"]?$nowt["k1start"]:$tiall[$i]["start"];
			$end=$tiall[$i]["end"]>=$nowt["k1end"]?$nowt["k1end"]:$tiall[$i]["end"];
			$ts=($end-$start)/3600/24+1;
			$tmp["feiyong"]=round($tiall[$i]["pizhunhanshui"]/$zongts*$ts,4);
			$tmp["hexiao"]=round($tiall[$i]["hexiao"]/$zongts*$ts,4);
		
			$buzangu+=$tmp["hexiao"];
		}

		$buzangu=round($buzangu,2);
	
		$whereti="o.status in(1,7,9) and apd.end>={$nowt["k1start"]} and apd.start<={$nowt["k1end"]}";
		$tiall=Db::table("cg_order_huobu")->alias("hb")
		->join("cg_ti_hexiao_list ahd","hb.hxdmxbh=ahd.id")
		->join("cg_ti_hexiao ahp","ahd.hx_id=ahp.id")
		->join("cg_order o","o.id=hb.order_id")
		->join("cg_ti_list apd","ahd.list_id=apd.id")
		->join("cg_jxs j","apd.jxs_id=j.id","LEFT")
		->join("cg_ti ap","apd.ti_id=ap.id")
		->join("employee e","o.sqr=e.id")
		->where($whereti)->order("hb.id,o.id")
		->field("j.sn as jxs_no,j.title as jxs_title,ap.sn as ap_no,o.sn as o_no,e.em_name,apd.sn as apd_no,ahp.sn as ahp_no,ahd.sn as ahd_no,apd.start,apd.end,hb.prod_jine")
		->select();
		//print_r($tiall);
		$buhuo=0;
		for($i=0;$i<count($tiall);$i++){
			
			$zongts=($tiall[$i]["end"]-$tiall[$i]["start"])/3600/24+1;
			$start=$tiall[$i]["start"]<=$nowt["k1start"]?$nowt["k1start"]:$tiall[$i]["start"];
			$end=$tiall[$i]["end"]>=$nowt["k1end"]?$nowt["k1end"]:$tiall[$i]["end"];
			$ts=($end-$start)/3600/24+1;
			$tmp["feiyong"]=round($tiall[$i]["prod_jine"]/$zongts*$ts,4);
		
			$buhuo-=$tmp["feiyong"];
		}
		$buhuo=round($buhuo,2);
	
		$zongji=$zanguzong+$jieanzong+$buzangu+$buhuo;
		
		    $eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();
	  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("{$ctitle}-{$k1title}费用汇总表");


          $objPHPExcel->getActiveSheet()->mergeCells('A1:D1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "{$ctitle}-{$k1title}费用汇总表");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:D2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);
            $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
	    
	     $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	     $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getFill()->getStartColor()->setARGB('0099CCFF');
	         //水平剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	      $objPHPExcel->getActiveSheet()->setCellValue('A3',"项目");
	      $objPHPExcel->getActiveSheet()->setCellValue('B3',"说明");
	      $objPHPExcel->getActiveSheet()->setCellValue('C3',"销售费用");
	      $objPHPExcel->getActiveSheet()->setCellValue('D3',"合计");


	       $objPHPExcel->getActiveSheet()->setCellValue('A4',"暂估");
	       $objPHPExcel->getActiveSheet()->setCellValue('A5',"结案");
	       $objPHPExcel->getActiveSheet()->setCellValue('A6',"补货X1");
	        $objPHPExcel->getActiveSheet()->setCellValue('A7',"补货X2");
	        $objPHPExcel->getActiveSheet()->setCellValue('B4','来自“暂估明细表”');
	        $objPHPExcel->getActiveSheet()->setCellValue('C4',$zanguzong);
	        $objPHPExcel->getActiveSheet()->setCellValue('D4',$zanguzong);
	         $objPHPExcel->getActiveSheet()->setCellValue('B5','来自“结案明细表”');
	        $objPHPExcel->getActiveSheet()->setCellValue('C5',$jieanzong);
	        $objPHPExcel->getActiveSheet()->setCellValue('D5',$jieanzong);
	          $objPHPExcel->getActiveSheet()->setCellValue('B6','来自“补货明细表”按核清价暂估');
	        $objPHPExcel->getActiveSheet()->setCellValue('C6',$buzangu);
	        $objPHPExcel->getActiveSheet()->setCellValue('D6',$buzangu);
	         $objPHPExcel->getActiveSheet()->setCellValue('B7','来自“补货明细表”按当月实际成本价');
	        $objPHPExcel->getActiveSheet()->setCellValue('C7',$buhuo);
	        $objPHPExcel->getActiveSheet()->setCellValue('D7',$buhuo);


	         $objPHPExcel->getActiveSheet()->setCellValue('A8',"合计");
	          $objPHPExcel->getActiveSheet()->mergeCells('A8:B8');
	        $objPHPExcel->getActiveSheet()->setCellValue('C8',$zongji);
	        $objPHPExcel->getActiveSheet()->setCellValue('D8',$zongji);

	          $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);


              $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:D8')->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        
          $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header("Content-Disposition:attachment;filename={$ctitle}-{$k1title}费用汇总表.xls");
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}
}